#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：tools 
@File ：成品合同匹配.py
@IDE  ：PyCharm
@Author ：李涵彬
@Date ：2024/11/18 下午4:49
"""

"""
需要操作合同表，物料表，减宽量配置表，以优充次配置表

1.读取四个excel表格，表格可能是csv、xls、xlsx格式文件
2.逐行遍历物料表，使用“出钢标记”在合同表中进行数据匹配，如果能够匹配到数据，则进行3、4，如果无法匹配到数据，则跳过3、4，从5开始执行
3.如果物料表中“推荐轧宽”列存在值，则解析该单元格的两个数值，分别作为范围最小值和范围最大值；使用该范围与合同表中的“轧宽”进行匹配，如果该物料轧宽在范围内，则该合同号符合要求，添加这个合同号
4.如果物料表中“推荐轧宽”列不存在值，则通过减宽量配置表解析计算“推荐轧宽”范围，需要首先获取合同表中的“全程途径码”的第9、第10位，在减宽量配置表中匹配“产线”列，“宽度小值”作为范围最小值，“宽度大值”作为范围最大值，之后使用该范围与合同表中的“轧宽”+对应“减宽量”进行匹配，如果在范围内，则添加这个合同号
5.如果使用“出钢标记”在合同表中进行数据匹配，不能够匹配到数据，则在以优充次配置表中，匹配“(材料)出钢标记”列，将对应的“(合同)出钢标记”作为“出钢标记”进行二次匹配，如果“钢级代码”列存在值，则使用“出钢标记”和“钢级代码”同时进行匹配，如果不存在值，则解析“宽度上限”“宽度下限”“厚度上限”“厚度下限”，使用这两个范围，分别对“轧宽”“轧厚”进行范围判定，符合范围内的，进行合同号添加
6.构建dataframe格式的result变量，第一列为材料号，第二列为出钢标记，之后，匹配到的合同号依次填充
7.最后将result保存到桌面
"""

import tkinter as tk
from tkinter import filedialog, messagebox, ttk, simpledialog
import pandas as pd
import ast
import logging
from pathlib import Path
from typing import List
import secrets
from cryptography.fernet import Fernet
import json
from datetime import datetime, timedelta

# 日志配置
LOG_DIR = Path(__file__).parent / 'logs'
LOG_DIR.mkdir(exist_ok=True)
LOG_FILE = LOG_DIR / 'app.log'

logging.basicConfig(
	level=logging.INFO,
	format='%(asctime)s - %(levelname)s - %(message)s',
	handlers=[logging.FileHandler(LOG_FILE), logging.StreamHandler()]
)

# 设置常量路径
REDUCTION_WIDTH_CONFIG_PATH = './config/热轧产线减宽量配置表.xlsx'
SUBSTITUTE_CONFIG_PATH = './config/以优充次配置表.xlsx'

# 注册码验证
LICENSE_FILE = "./config/encrypted_licenses.txt"

# 文件路径
LAST_VALIDATE_TIME_FILE = Path.home() / '.last_validate_time'


# 从配置文件加载密钥
def load_key_from_config(file_path):
	with open(file_path, 'r') as file:
		config = json.load(file)
	key = config.get('fernet_key')
	if key is None:
		raise ValueError("Fernet key not found in configuration file.")
	return key


# 保存最后一次验证时间到本地文件
def save_last_validate_time():
	with open(LAST_VALIDATE_TIME_FILE, 'w') as file:
		json.dump({'last_validate_time': last_validate_time.isoformat()}, file)


# 从本地文件读取最后一次验证时间
def load_last_validate_time():
	if LAST_VALIDATE_TIME_FILE.exists():
		with open(LAST_VALIDATE_TIME_FILE, 'r') as file:
			data = json.load(file)
			return datetime.fromisoformat(data['last_validate_time'])
	return None


# 加载加密的注册码
def load_encrypted_licenses():
	try:
		with open(LICENSE_FILE, "rb") as file:
			encrypted_licenses = file.read()
		decrypted_licenses = cipher_suite.decrypt(encrypted_licenses).decode()
		licenses = json.loads(decrypted_licenses)
		return licenses
	except FileNotFoundError:
		return {}


# 验证注册码
def validate_license_code(license_code, licenses, last_validate_time_global=None):
	current_time = datetime.now()
	if last_validate_time_global and (current_time - last_validate_time_global) < timedelta(days=7):
		# 如果上次验证时间在一周内，则直接返回True
		return True
	if license_code in licenses:
		expiration_date = datetime.fromisoformat(licenses[license_code])
		if current_time < expiration_date:
			# 更新最后一次验证时间
			global last_validate_time
			last_validate_time = current_time
			save_last_validate_time()  # 保存最后一次验证时间
			return True
	return False


def read_excel_file(file_path: str) -> pd.DataFrame:
	"""读取 Excel 文件，并处理可能的异常"""
	try:
		return pd.read_excel(file_path)
	except Exception as e:
		logging.error(f"无法读取文件 {file_path}: {e}")
		raise


def match_contracts_by_width(matched_contracts: pd.DataFrame, min_width: float, max_width: float) -> pd.DataFrame:
	"""通过轧宽范围匹配合同"""
	return matched_contracts[(matched_contracts['轧宽'] >= min_width) & (matched_contracts['轧宽'] <= max_width)]


def match_contracts_by_reduction_width(material_row_width: int, matched_contracts: pd.DataFrame,
									   reduction_width_config_df: pd.DataFrame) -> pd.DataFrame:
	"""通过减宽量配置表计算推荐轧宽范围"""
	matched_contracts_res = pd.DataFrame(columns=matched_contracts.columns)
	for index, row in matched_contracts.iterrows():
		reduction_config = reduction_width_config_df[reduction_width_config_df['产线'] == row['全程途径码'][8:10]]
		if reduction_config.empty:
			logging.warning(f"未找到减宽量配置")
		else:
			for _, sub_row in reduction_config.iterrows():
				if sub_row['宽度小值'] <= row['轧宽'] <= sub_row['宽度大值']:
					reduction_width = sub_row['减宽量']
					min_width, max_width = row['轧宽'], row['轧宽'] + reduction_width
					if min_width <= material_row_width <= max_width:
						matched_contracts_res = pd.concat([matched_contracts_res, pd.DataFrame([row])],
														  ignore_index=True)
						break
	return matched_contracts_res


def substitute_contracts(material_row_width: int, material_drawing_code: str, substitute_config_df: pd.DataFrame,
						 reduction_width_config_df: pd.DataFrame, contracts_df: pd.DataFrame) -> List[str]:
	"""通过以优充次配置表匹配合同"""
	# 筛选出以优充次物料对应配置表
	substitute_match = substitute_config_df[substitute_config_df['(材料)出钢标记'] == material_drawing_code]
	if substitute_match.empty:
		return []

	matched_contracts_res = pd.DataFrame(columns=contracts_df.columns)

	# 遍历以优充次配置表
	for index, row in substitute_match.iterrows():
		logging.info(f"开始匹配以优充次配置：{row['(合同)出钢标记']}")

		substitute_drawing_code = row['(合同)出钢标记']
		substitute_sign_code = row['钢级代码']
		# 筛选出以优充次出钢标记和钢级代码对应的合同
		# 如果钢级代码字段非空，则使用出钢标记和钢级代码进行匹配
		if substitute_sign_code != '' and substitute_sign_code != ' ':
			substitute_contracts = contracts_df[
				(contracts_df['出钢标记'] == substitute_drawing_code) & (
						contracts_df['钢级代码'] == substitute_sign_code)]
		else:
			# 如果钢级代码字段为空，则使用出钢标记进行匹配
			substitute_contracts = contracts_df[contracts_df['出钢标记'] == substitute_drawing_code]

		if substitute_contracts.empty:
			logging.warning(f"未找到以优充次配置匹配的合同：{row['(合同)出钢标记']}")
			continue
		else:
			# 如果存在以优充次合同，则使用规格宽度、规格厚度进行匹配
			min_width, max_width, min_thickness, max_thickness = row[
				['宽度下限', '宽度上限', '厚度下限', '厚度上限']]
			substitute_contracts = substitute_contracts[
				(substitute_contracts['轧宽'] >= min_width) & (substitute_contracts['轧宽'] <= max_width) &
				(substitute_contracts['轧厚'] >= min_thickness) & (substitute_contracts['轧厚'] <= max_thickness)
				]

		substitute_contracts = match_contracts_by_reduction_width(material_row_width, substitute_contracts,
																  reduction_width_config_df)

		matched_contracts_res = pd.concat([matched_contracts_res, substitute_contracts], ignore_index=True)

	return matched_contracts_res['合同号'].dropna().unique().tolist()


def process_materials(materials_df: pd.DataFrame, contracts_df: pd.DataFrame, reduction_width_config_df: pd.DataFrame,
					  substitute_config_df: pd.DataFrame, progress_var: tk.DoubleVar) -> pd.DataFrame:
	"""处理所有物料行并匹配合同号"""
	result = []
	for index, material_row in materials_df.iterrows():
		material_drawing_code = material_row['出钢标记']
		material_number = material_row['材料号']
		material_recommend_width = material_row.get('推荐轧宽')

		logging.info(
			f"处理物料行：{index + 1}/{len(materials_df)}，材料号：{material_number}，出钢标记：{material_drawing_code}")

		matched_contracts = contracts_df[contracts_df['出钢标记'] == material_drawing_code]
		contract_numbers = []

		# 如果出钢标记筛选不为空则执行正常一次匹配
		if not matched_contracts.empty:
			if material_recommend_width != '' and material_recommend_width != ' ':
				# 如果存在推荐轧宽，则根据推荐轧宽匹配合同
				min_width, max_width = ast.literal_eval(material_recommend_width)
				matched_contracts = match_contracts_by_width(matched_contracts, min_width, max_width)
			else:
				# 如果不存在推荐轧宽，则根据减宽量配置表匹配合同
				matched_contracts = match_contracts_by_reduction_width(material_row['宽(mm)'], matched_contracts,
																	   reduction_width_config_df)
			contract_numbers = matched_contracts['合同号'].dropna().unique().tolist()
		else:
			# 如果出钢标记筛选为空，则执行以优充次匹配
			contract_numbers = substitute_contracts(material_row['宽(mm)'], material_drawing_code, substitute_config_df,
													reduction_width_config_df, contracts_df)

		if contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		# res_contract_numbers = []
		# result.append({
		# 	'材料号': material_number,
		# 	'出钢标记': material_drawing_code,
		# 	'合同号': ','.join(res_contract_numbers) if res_contract_numbers else None
		# })
		#
		# # 更新进度条
		# progress_var.set(index / len(materials_df))
		# continue

		# 机组特殊指令筛选
		# 如果物料机组特殊指令中包含“4.0mm”，则合同中的“轧厚”需要大于4.0
		if '4.0mm' in str(material_row['机组特殊指令']):
			for contract_number in contract_numbers:
				tmp_match_contract = contracts_df[contracts_df['合同号'] == contract_number]
				if float(tmp_match_contract['轧厚']) < 4:
					contract_numbers.remove(contract_number)

		if contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		# 吨重能否分配进行校验
		res_contract_numbers = []
		for contract_number in contract_numbers:
			contract_row = contracts_df[contracts_df['合同号'] == contract_number]
			min_weight = float(contract_row['订货重量单件最小值'])
			max_weight = float(contract_row['订货重量单件最大值'])
			for i in range(1, 4):
				sub_min_weight = i * min_weight
				sub_max_weight = i * max_weight
				if sub_min_weight <= material_row['重量(t)'] <= sub_max_weight:
					res_contract_numbers.append(contract_number)
					break

		if res_contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		result.append({
			'材料号': material_number,
			'出钢标记': material_drawing_code,
			'合同号': ','.join(res_contract_numbers) if res_contract_numbers else None
		})

		# 更新进度条
		progress_var.set(index / len(materials_df))

	result_df = pd.DataFrame(result)

	split_contract_columns = result_df['合同号'].str.split(',', expand=True)
	max_columns = split_contract_columns.shape[1]
	for i in range(max_columns):
		result_df[f'合同号{i + 1}'] = split_contract_columns[i]

	result_df.drop('合同号', axis=1, inplace=True)

	return result_df


def save_result_to_excel(result_df: pd.DataFrame, file_path: str):
	"""保存结果到Excel文件"""
	result_df.to_excel(file_path, index=False)


def main(contract_path, material_path, reduction_width_config_path, substitute_config_path, progress_var):
	# 读取文件
	contracts_df = read_excel_file(contract_path)
	materials_df = read_excel_file(material_path)
	reduction_width_config_df = read_excel_file(reduction_width_config_path)
	substitute_config_df = read_excel_file(substitute_config_path)

	# 处理物料并匹配合同号
	result_df = process_materials(materials_df, contracts_df, reduction_width_config_df, substitute_config_df,
								  progress_var)

	# 保存到桌面
	desktop_path = Path.home() / 'Desktop'
	result_path = desktop_path / 'result.xlsx'
	save_result_to_excel(result_df, result_path)
	logging.info(f"结果已保存到桌面路径：{result_path}")
	messagebox.showinfo("完成", "处理完成，结果已保存到桌面。")


def browse_file():
	"""浏览文件对话框"""
	file_path = filedialog.askopenfilename(
		title="选择文件",
		filetypes=[("Excel files", "*.xlsx *.xls *.csv")]
	)
	return file_path


def start_execution(progress_var):
	# 获取文件路径
	contract_path = contract_entry.get()
	material_path = material_entry.get()

	# 检查文件路径是否已选择
	if not contract_path or not material_path:
		messagebox.showerror("错误", "请先选择所有文件")
		return

	# 弹出注册码输入框并验证注册码
	licenses = load_encrypted_licenses()
	if not validate_license_code(None, licenses, last_validate_time):
		user_key = simpledialog.askstring("注册码验证", "请输入您的注册码：", parent=root)
		if not user_key or not validate_license_code(user_key, licenses, last_validate_time):
			messagebox.showerror("错误", "注册码无效或已过期。")
			return

	# 调用原始脚本的main函数，传入文件路径
	main(contract_path, material_path, REDUCTION_WIDTH_CONFIG_PATH, SUBSTITUTE_CONFIG_PATH, progress_var)


config_file_path = './config/config.json'  # 配置文件路径
cipher_suite = Fernet(load_key_from_config(config_file_path))

# 上一次验证时间
last_validate_time = load_last_validate_time()

# 创建主窗口
root = tk.Tk()
root.title("成品合同匹配工具")

# 设置窗口大小，大约和微信窗口一个大小
root.geometry("800x600")

# 设置字体大小
font_size = 14

# 创建标签和输入框
tk.Label(root, text="热轧合同明细路径:", font=("Arial", font_size)).pack(pady=5)
contract_entry = tk.Entry(root, font=("Arial", font_size), width=50)
contract_entry.pack(pady=5)

contract_button = tk.Button(root, text="浏览", command=lambda: contract_entry.insert(0, filedialog.askopenfilename(
	title="选择文件", filetypes=[("Excel files", "*.xlsx *.xls *.csv")])), font=("Arial", font_size))
contract_button.pack(pady=5)

tk.Label(root, text="余材明细表路径:", font=("Arial", font_size)).pack(pady=5)
material_entry = tk.Entry(root, font=("Arial", font_size), width=50)
material_entry.pack(pady=5)

material_button = tk.Button(root, text="浏览", command=lambda: material_entry.insert(0, filedialog.askopenfilename(
	title="选择文件", filetypes=[("Excel files", "*.xlsx *.xls *.csv")])), font=("Arial", font_size))
material_button.pack(pady=5)

# 创建进度条
progress_var = tk.DoubleVar()
progress_bar = ttk.Progressbar(root, length=200, variable=progress_var, maximum=1.0)
progress_bar.pack(pady=20)

# 创建开始执行按钮
start_button = tk.Button(root, text="开始执行", command=lambda: start_execution(progress_var),
						 font=("Arial", font_size))
start_button.pack(pady=20)

# 运行主循环
root.mainloop()
